using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;

namespace FederationMembershipDB
{
	public class Membership
	{
        int _memberID;//ID of the Member buying the Membership
        string _payee;
		string _membershipType;
		RegistrationFee _registrationFee;
		Academy _affiliatedAcademy;
		DateTime _commencementDate;
		MembershipStatus _membershipStatus;
		DateTime _endDate;

        public int MemberID
        { get { return _memberID; } set { _memberID = value; } }
        public string Payee
        {
            get { return _payee; }
            set { _payee = value; }
        }
        public string MembershipType
        {
            get { return _membershipType; }
            set { _membershipType = value; }
        }
        public RegistrationFee RegistrationFee
        {
            get { return _registrationFee; }
            set { _registrationFee = value; }
        }
        public Academy AffiliatedAcademy
        {
            get { return _affiliatedAcademy; }
            set { _affiliatedAcademy = value; }
        }
        public DateTime CommencementDate
        {
            get { return _commencementDate; }
            set { _commencementDate = value; }
        }
        public MembershipStatus MembershipStatus
        {
            get { return _membershipStatus; }
            set { _membershipStatus = value; }
        }
        public DateTime MembershipExpiry
        {
            get { return _endDate; }
            set { _endDate = value; }
        }
		
        public void AddMembershipDetails(ref int membershipID, int RegFeeID)
		{
            //Parameter: ref int ChequeID - when cheque ID is set, the int variable passed in will be updated in the calling function

            //           SqlTransaction - to keep the transaction as a whole unit, pass in an existing Transaction

            //get the ID of the Membership Status Type
            int intStatusTypeID = -1;
            string sqlStatusType = "SELECT ID FROM MemberShipStatus WHERE Type ='" + MembershipStatus.MembershipStatusType + "';";
            DataSet dsStatusType = Utilities.SearchDatabase(sqlStatusType);
            intStatusTypeID = int.Parse(dsStatusType.Tables[0].Rows[0].ItemArray.GetValue(0).ToString());            


            //Get the last ID in the DB
            string sqlSelectID = "SELECT TOP(1) ID FROM MemberShip Order By ID DESC;";
            DataSet ds = Utilities.SearchDatabase(sqlSelectID);

            //check to see if it will be the first item inserted.
            if (ds.Tables[0].Rows.Count == 0)
            {
                membershipID = 0;
            }
            else
            {
                membershipID = int.Parse(ds.Tables[0].Rows[0].ItemArray.GetValue(0).ToString()) + 1; // +1 to increment - the next chequeID
            }

            //Construct INSERT statement
            string sqlInsert = "INSERT INTO MemberShip VALUES ('" + membershipID + "','" + MemberID + "','" + Payee + "','" + 
                MembershipType + "','" + RegFeeID + "','" + AffiliatedAcademy.AcademyName + "', CONVERT(datetime, '" + CommencementDate + "', 103),'" +
                intStatusTypeID + "',CONVERT(datetime, '" + MembershipExpiry + "', 103));";
                //MembershipStatus.MembershipStatusType + "',CONVERT(datetime, '" + MembershipExpiry + "', 103));";

            Utilities.DatabaseNonQuery(sqlInsert);
		}
		
	}
}
